from .Query import Query


class CountFailByMajor(Query):
    __sql = """
    SELECT
	course.TERM,
	course.ACADEMICYEAR,
	COURSECODE,
	COURSENAME,
	COUNT(course.STUDENTNO) no_pass
    FROM
        t_courseinfo_students as course
    JOIN t_stuscoreconf_master students ON course.STUDENTNO = students.STUDENTNO
    WHERE
        (
            FINALSCORE IN ("不通过", "不及格")
            OR FINALSCORE IS NULL
            OR FINALSCORE < "60"
        )
    AND students.MAJOR = %s
    AND students.GRADE = %s
    AND course.ACADEMICYEAR LIKE %s
    AND course.TERM LIKE %s
    GROUP BY
        course.TERM,
        course.ACADEMICYEAR,
        COURSECODE,
        COURSENAME
    ORDER BY
        no_pass DESC
   """

    def __init__(self, request):
        major = Query.len_zero_to_default(request.get("major", "%"))
        grade = Query.len_zero_to_default(request.get("grade", "%"))
        term = Query.len_zero_to_default(request.get("term", "%"))
        academic_year = Query.len_zero_to_default(
            request.get("academic_year", "%"))
        self.execute_sql(self.__sql, major, grade, academic_year, term)
